﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Xml.Linq;
using Wintellect.PowerCollections;

namespace DatingService
{
    class DatingUtils
    {
        public void ProcesXmlFile() 
        {
            DateTime start = DateTime.Now;
            string path = @"..\..\people.xml";
            string outputPath = @"..\..\dates.xml";
            Console.WriteLine("Start time: {0}", start);
            FillDataBase(path);
            Console.WriteLine("Finding dates.");
            var allDates = FindDates();
            ExtractDatesToXml(allDates, outputPath);
            Console.WriteLine("Extracting dates into database.");
            ExtractDatesToDatabase(allDates);
            Console.WriteLine("Completed.");
            Console.WriteLine(DateTime.Now - start);
        }

        private void FillDataBase(string filePath) 
        {
            SqlConnection dbCon = new SqlConnection("Server=.\\SQLEXPRESS; " +
                "Database= DatingDatabase; Integrated Security=true");
            dbCon.Open();
            using(dbCon)
            {
                XDocument peopleDoc = XDocument.Load(filePath);
                var guys = peopleDoc.Descendants("guys");
                var girls = peopleDoc.Descendants("girl");
                AddPeopleToDb(dbCon,guys,"guy");
                AddPeopleToDb(dbCon, girls, "girls");
            }
        }
  
        private void AddPeopleToDb(SqlConnection dbCon, IEnumerable<XElement> people, string sex)
        {
            var person = people.Descendants("person");
            foreach (var guy in person) 
            {
                string name = null;
                int? age = null;
                string hairColor = null;
                string eyeColor = null;
                int? height = null;
                foreach (var attribute in guy.Attributes())
                {
                    switch (attribute.Name.ToString())
                    {
                        case "name":
                            name = attribute.Value;
                            break;
                        case "age":
                            age = int.Parse(attribute.Value);
                            break;
                        case "haircolor":
                            hairColor = attribute.Value;
                            break;
                        case "eyecolor":
                            eyeColor = attribute.Value;
                            break;
                        case "height":
                            height = int.Parse(attribute.Value);
                            break;
                        default:
                            throw new ArgumentException(
                                "Invalid attribute for person: {0}",
                                attribute.Name.ToString());
                    }
                    AddPersonToDb(dbCon, name, age, hairColor, eyeColor, height, sex);
                    FindPreferences(dbCon, guy.Descendants("preferences"));
                }
            }
        }
  
        private void AddPersonToDb(SqlConnection dbCon, string name, int? age, string hairColor, 
            string eyeColor, int? height, string sex)
        {
            SqlCommand insertCommand = new SqlCommand();
            insertCommand.Connection = dbCon;
            insertCommand.CommandText =
                "INSERT INTO People (Name, Sex, Age, HairColor, EyeColor, Height)\n" +
                "VALUES (@name, @sex, @age, @hairColor, @eyeColor, @height)";
            insertCommand.Parameters.Add("@name", name);
            insertCommand.Parameters.Add("@sex", sex);
            insertCommand.Parameters.Add("@age", age);
            insertCommand.Parameters.Add("@hairColor", hairColor);
            insertCommand.Parameters.Add("@eyeColor", eyeColor);
            insertCommand.Parameters.Add("@height", height);
            insertCommand.ExecuteNonQuery();
        }

        private void FindPreferences(
            SqlConnection dbConnection, IEnumerable<XElement> preferences)
        {
            SqlCommand findPersonId = new SqlCommand();
            findPersonId.Connection = dbConnection;
            findPersonId.CommandText = "SELECT MAX(PersonID) FROM Persons";
            int personID = (int)findPersonId.ExecuteScalar();
            foreach (var preference in preferences)
            {
                var guys = preference.Descendants("guy");
                AddPersonsPreferencesToDb(dbConnection, guys, personID, "guy");
                var girls = preference.Descendants("girl");
                AddPersonsPreferencesToDb(dbConnection, girls, personID, "girl");
            }
        }

        private  void AddPersonsPreferencesToDb(SqlConnection dbConnection,
            IEnumerable<XElement> persons, int personId, string sex)
        {
            List<int?> ageMins = new List<int?>();
            List<int?> ageMaxs = new List<int?>();
            List<string> hairColors = new List<string>();
            List<string> eyeColors = new List<string>();
            List<int?> heightMins = new List<int?>();
            List<int?> heightMaxs = new List<int?>();
            foreach (var person in persons)
            {
                int? ageMin = null;
                int? ageMax = null;
                string hairColor = null;
                string eyeColor = null;
                int? heightMin = null;
                int? heightMax = null;
                foreach (var item in person.Descendants())
                {
                    switch (item.Name.ToString())
                    {
                        case "age":
                            string[] ageString = item.Value.Split('-');
                            ageMin = int.Parse(ageString[0]);
                            ageMax = int.Parse(ageString[1]);
                            break;
                        case "height":
                            string[] heightString = item.Value.Split('-');
                            heightMin = int.Parse(heightString[0]);
                            heightMax = int.Parse(heightString[1]);
                            break;
                        case "haircolor":
                            hairColor = item.Value;
                            break;
                        case "eyecolor":
                            eyeColor = item.Value;
                            break;
                        default:
                            throw new ArgumentException(
                                "Invalid descendant: {0}", item.Name.ToString());
                    }
                }
                ageMins.Add(ageMin);
                ageMaxs.Add(ageMax);
                hairColors.Add(hairColor);
                eyeColors.Add(eyeColor);
                heightMins.Add(heightMin);
                heightMaxs.Add(heightMax);
                //AddPreferenceToDb(dbConnection, personId, sex, ageMin, ageMax, hairColor, eyeColor, heightMin, heightMax);
            }
            if (ageMins.Count > 0)
            {
                AddPreferencesToDb(dbConnection, personId, sex, ageMins, ageMaxs,
                hairColors, eyeColors, heightMins, heightMaxs);
            }
        }

        private void AddPreferencesToDb(SqlConnection dbConnection,
            int personID, string sex,
            List<int?> ageMins, List<int?> ageMaxs,
            List<string> hairColors, List<string> eyeColors,
            List<int?> heightMins, List<int?> heightMaxs)
        {
            StringBuilder sb = new StringBuilder(
                "INSERT INTO Preferences (PersonID, Sex, AgeMin, AgeMax, " +
                "HairColor, EyeColor, HeightMin, HeightMax) \n" +
                "VALUES ");
            for (int i = 0; i < ageMins.Count; i++)
            {
                sb.Append(string.Format("({0}, '{1}', {2}, {3}, '{4}', '{5}', {6}, {7}), ",
                    personID, sex, ageMins[i], ageMaxs[i], hairColors[i],
                    eyeColors[i], heightMins[i], heightMaxs[i]));
            }
            SqlCommand insertCommand = new SqlCommand(sb.ToString(0, sb.Length - 2), dbConnection);
            insertCommand.ExecuteNonQuery();
        }

        public OrderedMultiDictionary<string, string> FindDates()
        {
            var dates = new OrderedMultiDictionary<string, string>(false);
            SqlConnection dbConnection = new SqlConnection("Server=.\\SQLEXPRESS; " +
                "Database = DatingDatabase; Integrated Security=true");
            dbConnection.Open();
            using (dbConnection)
            {
                SqlCommand findDatesCommand = new SqlCommand();
                findDatesCommand.Connection = dbConnection;
                findDatesCommand.CommandText =
                    "SELECT p.Name AS [PersonName], t.Name AS [TargetName] " +
                    "FROM Persons AS p " +
                    "JOIN Preferences AS pr " +
                        "ON p.PersonID = pr.PersonID " +
                    "JOIN Persons AS t " +
                        "ON t.Sex = pr.Sex AND " +
                            "t.Age BETWEEN pr.AgeMin AND pr.AgeMax AND " +
                            "t.Height BETWEEN pr.HeightMin AND pr.HeightMax AND " +
                            "pr.EyeColor LIKE '%' + t.EyeColor + '%' AND " +
                            "pr.HairColor LIKE '%' + t.HairColor + '%' AND " +
                            "t.PersonID != p.PersonID";

                var commandResult = findDatesCommand.ExecuteReader();
                using (commandResult)
                {
                    while (commandResult.Read())
                    {
                        string firstPerson = commandResult["PersonName"].ToString();
                        string secondPerson = commandResult["TargetName"].ToString();
                        if (firstPerson.CompareTo(secondPerson) <= 0)
                        {
                            dates.Add(firstPerson, secondPerson);
                        }
                        else
                        {
                            dates.Add(secondPerson, firstPerson);
                        }
                    }
                }
            }
            return dates;
        }

        public void ExtractDatesToXml(
            OrderedMultiDictionary<string, string> dates, string xmlOutputPath)
        {
            XElement root = new XElement("dates");
            XDocument datesDoc = new XDocument(root);
            foreach (var date in dates)
            {
                foreach (var item in date.Value)
                {
                    XElement element = new XElement("date");
                    element.Add(new XElement("person", new XAttribute("name", date.Key)));
                    element.Add(new XElement("person", new XAttribute("name", item)));
                    root.Add(element);
                }
            }
            datesDoc.Save(xmlOutputPath);
        }

        public void ExtractDatesToDatabase(
            OrderedMultiDictionary<string, string> dates)
        {
            SqlConnection dbConnection = new SqlConnection("Server=.\\SQLEXPRESS; " +
               "Database= DatingUtils; Integrated Security=true");
            dbConnection.Open();
            using (dbConnection)
            {
                SqlCommand dropTableCommand = new SqlCommand();
                dropTableCommand.Connection = dbConnection;
                dropTableCommand.CommandText =
                    "IF EXISTS(SELECT * FROM sys.tables where name='Dates') \n" +
                    "BEGIN \n" +
                        "DROP TABLE Dates \n" +
                    "END";
                dropTableCommand.ExecuteNonQuery();

                SqlCommand createTableCommand = new SqlCommand();
                createTableCommand.Connection = dbConnection;
                createTableCommand.CommandText =
                    "CREATE TABLE Dates ( " +
                    "DateID int IDENTITY PRIMARY KEY, " +
                    "Name1 nvarchar(50) NOT NULL, " +
                    "Name2 nvarchar(50) NOT NULL)";
                createTableCommand.ExecuteNonQuery();

                SqlCommand insertIntoTableCommand = new SqlCommand();
                insertIntoTableCommand.Connection = dbConnection;
                insertIntoTableCommand.CommandText =
                    "INSERT INTO Dates (Name1, Name2) " +
                    "VALUES (@name1, @name2)";
                insertIntoTableCommand.Parameters.Add("@name1", null);
                insertIntoTableCommand.Parameters.Add("@name2", null);
                foreach (var date in dates)
                {
                    foreach (var item in date.Value)
                    {
                        insertIntoTableCommand.Parameters["@name1"].Value = date.Key;
                        insertIntoTableCommand.Parameters["@name2"].Value = item;
                        insertIntoTableCommand.ExecuteNonQuery();
                    }
                }
            }
        }

        private static object GetDbValue(object value)
        {
            if (value != null)
            {
                return value;
            }
            else
            {
                return DBNull.Value;
            }
        }
    }
}
